﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FreeOnlineApplication.Class.Entity;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Collections;

namespace FreeOnlineApplication.Class.Repository
{
    public class UserRepository
    {
        protected MySqlConnection m_connection;
        MySqlCommand aCommand = new MySqlCommand();
        Crypto m_crypto = new Crypto();

        public UserRepository(MySqlConnection connection)
        {
            m_connection = connection;
            aCommand.Connection = m_connection;
        }
        public void Insert(User user)
        {
            User old = GetByCode(user.CODE, user.UNIT.ID);
            if (old != null) throw new Exception("User code is already used!");

            string insertSQL = String.Format(@"insert into table_user 
                (   user_code,
                    user_name,
                    unit_id, 
                    user_password) 
                VALUES ('{0}','{1}',{2},'{3}')",
                user.CODE, 
                user.NAME, 
                user.UNIT.ID, 
                m_crypto.Encrypt(user.PASSWORD));
            aCommand.CommandText = insertSQL;
            aCommand.ExecuteNonQuery();
            user.ID = GetMaximumId();
        }
        public User GetById(int id)
        {
            string getByID = String.Format("select * from table_user where user_id = {0}", id);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            User user = transform(aReader);
            aReader.Close();
            return user;
        }
        public void Update(User user)
        {
            User old = GetByCode(user.CODE, user.UNIT.ID);
            if (old != null)
            {
                if (old.ID != user.ID)
                throw new Exception("User code is already used!");
            }

            string updateSQL = String.Format(@"update table_user set 
                user_code = '{0}', 
                user_name='{1}',
                unit_id='{2}',
                user_password = '{3}'
                where user_id = {4}",
                user.CODE, 
                user.NAME, 
                user.UNIT.ID, 
                m_crypto.Encrypt(user.PASSWORD),
                user.ID
                );
            aCommand.CommandText = updateSQL;
            aCommand.ExecuteNonQuery();
        }
        public int GetMaximumId()
        {
            string getByID = String.Format("SELECT max(user_id) from table_user");
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public int GetCount(int unit)
        {
            string getByID = String.Format("SELECT count(*) from table_user where unit_id = {0}", unit);
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public IList GetAll(int unit)
        {
            string sql = String.Format("select * from table_user where unit_id = {0}", unit);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        public void Delete(User user)
        {
            string deleteSQL = "delete from table_user where user_id = " + user.ID;
            aCommand.CommandText = deleteSQL;
            aCommand.ExecuteNonQuery();
        }
        public User GetByCode(string code, int unitID)
        {
            string getByID = String.Format("select * from table_user where user_code = '{0}' and unit_id = {1}", code, unitID);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            User user = transform(aReader);
            aReader.Close();
            return user;
        }
        private User transform(MySqlDataReader aReader)
        {
            User user = null;
            while (aReader.Read())
            {
                user = new User();
                user.ID = Convert.ToInt32(aReader["user_id"]);
                user.CODE = aReader["user_code"].ToString();
                user.NAME = aReader["user_name"].ToString();
                user.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                user.PASSWORD = m_crypto.Decrypt(aReader["user_password"].ToString());
            }
            return user;
        }
        private IList transformList(MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                User user = new User();
                user.ID = Convert.ToInt32(aReader["user_id"]);
                user.CODE = aReader["user_code"].ToString();
                user.NAME = aReader["user_name"].ToString();
                user.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                user.PASSWORD = m_crypto.Decrypt(aReader["user_password"].ToString());
                result.Add(user);
            }
            return result;
        }
    }
}
